﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Kanban.Repository
{
    public class RepositoryBase : IRepository
    {

        public const string PREFIXO_TABELA = "TB_DY_KB_";

        protected List<T> Listar<T>(string sql, Dictionary<string, object> parametros, Func<SqlDataReader, T> popularObjeto)
        {
            sql = this.AdicionarPrefixoTabelas(sql);

            using (SqlConnection conexaoSql = ConexaoSql.ObterConexao())
            {
                SqlCommand comandoSql = conexaoSql.CreateCommand();
                comandoSql.CommandText = sql;

                // Adiciona os parâmetros na query
                if (parametros != null)
                {
                    foreach (KeyValuePair<string, object> parametro in parametros)
                    {
                        comandoSql.Parameters.AddWithValue(parametro.Key, parametro.Value ?? DBNull.Value);
                    }
                }

                SqlDataReader leitorSql = comandoSql.ExecuteReader();

                List<T> lista = null;

                if (leitorSql.HasRows)
                {

                    lista = new List<T>();

                    while (leitorSql.Read())
                    {
                        // Converte a linha da tabela em um objeto
                        T item = popularObjeto(leitorSql);
                        lista.Add(item);
                    }
                }

                return lista;
            }
        }

        protected bool Consultar(string sql, Dictionary<string, object> parametros)
        {
            sql = this.AdicionarPrefixoTabelas(sql);

            using (SqlConnection conexaoSql = ConexaoSql.ObterConexao())
            {
                SqlCommand comandoSql = conexaoSql.CreateCommand();
                comandoSql.CommandText = sql;

                foreach (KeyValuePair<string, object> parametro in parametros)
                {
                    comandoSql.Parameters.AddWithValue(parametro.Key, parametro.Value);
                }

                SqlDataReader leitorSql = comandoSql.ExecuteReader();

                return leitorSql.HasRows;
            }

        }

        protected T Obter<T>(string sql, Dictionary<string, object> parametros, Func<SqlDataReader, T> popularObjeto)
        {
            sql = this.AdicionarPrefixoTabelas(sql);

            using (SqlConnection conexaoSql = ConexaoSql.ObterConexao())
            {
                SqlCommand comandoSql = conexaoSql.CreateCommand();
                comandoSql.CommandText = sql;

                foreach (KeyValuePair<string, object> parametro in parametros)
                {
                    comandoSql.Parameters.AddWithValue(parametro.Key, parametro.Value);
                }

                SqlDataReader leitorSql = comandoSql.ExecuteReader();

                T item = default(T);

                if (leitorSql.Read())
                {
                    item = popularObjeto(leitorSql);
                }

                return item;
            }
        }        

        protected T ObterPorId<T>(object id, Func<SqlDataReader, T> popularObjeto)
        {
            using (SqlConnection conexaoSql = ConexaoSql.ObterConexao())
            {
                SqlCommand comandoSql = conexaoSql.CreateCommand();
                comandoSql.CommandText = string.Format("SELECT * FROM {0} WHERE Id = @Id", this.ObterNomeTabela<T>());

                comandoSql.Parameters.AddWithValue("@Id", id);

                SqlDataReader leitorSql = comandoSql.ExecuteReader();

                T item = default(T);

                if (leitorSql.Read())
                {
                    item = popularObjeto(leitorSql);
                }

                return item;
            }
        }

        protected object Adicionar<T>(Dictionary<string, object> valores)
        {

            string tabela = this.ObterNomeTabela<T>();

            var sql = new StringBuilder("INSERT INTO ");
            sql.Append(tabela);
            sql.Append("(");
            int i = 0;
            foreach (KeyValuePair<string, object> valor in valores)
            {
                if (i == 0)
                {
                    i++;
                }
                else
                {
                    sql.Append(", ");
                }
                sql.Append(valor.Key);
            }
            sql.Append(") VALUES(");
            i = 0;
            foreach (KeyValuePair<string, object> valor in valores)
            {
                if (i == 0)
                {
                    i++;
                }
                else
                {
                    sql.Append(", ");
                }
                sql.Append("@").Append(valor.Key);
            }
            sql.Append("); SELECT @@IDENTITY");


            using (SqlConnection conexaoSql = ConexaoSql.ObterConexao())
            {
                SqlCommand comandoSql = conexaoSql.CreateCommand();
                comandoSql.CommandText = sql.ToString();

                foreach (KeyValuePair<string, object> parametro in valores)
                {
                    comandoSql.Parameters.AddWithValue("@" + parametro.Key, parametro.Value ?? DBNull.Value);
                }

                return comandoSql.ExecuteScalar();
            }
        }

        protected void Remover<T>(object id)
        {

            using (SqlConnection conexaoSql = ConexaoSql.ObterConexao())
            {
                SqlCommand comandoSql = conexaoSql.CreateCommand();
                comandoSql.CommandText = string.Format("DELETE FROM {0} WHERE Id = @Id", this.ObterNomeTabela<T>());
                comandoSql.Parameters.AddWithValue("@Id", id);
                comandoSql.ExecuteNonQuery();
            }
        }

        protected void RemoverLogicamente<T>(object id)
        {

            using (SqlConnection conexaoSql = ConexaoSql.ObterConexao())
            {
                SqlCommand comandoSql = conexaoSql.CreateCommand();
                comandoSql.CommandText = string.Format("UPDATE {0} SET Excluido = 1 WHERE Id = @Id", this.ObterNomeTabela<T>());
                comandoSql.Parameters.AddWithValue("@Id", id);
                comandoSql.ExecuteNonQuery();
            }
        }

        protected void Atualizar<T>(object id, Dictionary<string, object> parametros)
        {
            using (SqlConnection conexaoSql = ConexaoSql.ObterConexao())
            {
                SqlCommand comandoSql = conexaoSql.CreateCommand();
                
                comandoSql.Parameters.AddWithValue("@Id", id);

                var valores = new StringBuilder();
                int i = 0;
                foreach (KeyValuePair<string, object> p in parametros)
                {
                    if (i > 0)
                    {
                        valores.Append(", ");                        
                    }

                    valores.Append(p.Key).Append(" = ").Append("@").Append(p.Key);
                    comandoSql.Parameters.AddWithValue(string.Concat("@", p.Key), p.Value ?? DBNull.Value);

                    i++;
                }

                comandoSql.CommandText = string.Format("UPDATE {0} SET {1} WHERE Id = @Id", this.ObterNomeTabela<T>(), valores.ToString());

                comandoSql.ExecuteNonQuery();
            }
        }

        protected void Executar(string sql, Dictionary<string, object> parametros)
        {
            sql = this.AdicionarPrefixoTabelas(sql);

            using (SqlConnection conexaoSql = ConexaoSql.ObterConexao())
            {
                SqlCommand comandoSql = conexaoSql.CreateCommand();

                if (parametros != null)
                {
                    foreach (KeyValuePair<string, object> p in parametros)
                    {
                        comandoSql.Parameters.AddWithValue(string.Concat("@", p.Key), p.Value ?? DBNull.Value);
                    }                    
                }

                comandoSql.CommandText = sql;
                comandoSql.ExecuteNonQuery();
            }
        }

        protected object Adicionar(string sql, Dictionary<string, object> parametros)
        {
            sql = this.AdicionarPrefixoTabelas(sql);

            using (SqlConnection conexaoSql = ConexaoSql.ObterConexao())
            {
                SqlCommand comandoSql = conexaoSql.CreateCommand();

                if (parametros != null)
                {
                    foreach (KeyValuePair<string, object> p in parametros)
                    {
                        comandoSql.Parameters.AddWithValue(string.Concat("@", p.Key), p.Value ?? DBNull.Value);
                    }
                }

                comandoSql.CommandText = sql;
                return comandoSql.ExecuteScalar();
            }
        }

        protected string AdicionarPrefixoTabelas(string sql)
        {
            return sql.Replace("[PREFIXO_TABELA]", PREFIXO_TABELA);
        }

        protected string ObterNomeTabela<T>()
        {
            return string.Concat(PREFIXO_TABELA, typeof(T).Name);
        }
    }
}
